package org.example.inventory.dao.impl;

import org.example.inventory.dao.SaleDao;
import org.example.inventory.entity.Sale;
import org.example.inventory.exception.JdbcException;
import org.example.inventory.util.JdbcUtil;
import org.example.inventory.util.ResultSetHandler;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.*;

public class SaleDaoImpl implements SaleDao {
    private static ResultSetHandler<Sale> handler= rs -> {
        try {
            return new Sale(
                    rs.getInt("id"),
                    rs.getInt("product_id"),
                    rs.getInt("quantity"),
                    rs.getBigDecimal("sale_price"),
                    rs.getInt("operator_id"),
                    rs.getTimestamp("sale_time").toLocalDateTime()
            );
        }catch (SQLException e) {
            throw new JdbcException("结果集映射失败",e);
        }
    };

    @Override
    public Sale insertSale(Sale sale) {
        String sql="insert into sale (product_id,quantity,sale_price,operator_id) values(?,?,?,?)";
        JdbcUtil.executeUpdate(sql,sale.getProductId(),sale.getQuantity(),sale.getSalePrice(),sale.getOperatorID());
        String getLastIdSql="select LAST_INSERT_ID()";
        Integer id=JdbcUtil.executeQuerySingle(getLastIdSql,rs -> rs.getInt(1));
        //填充id并返回
        return selectSaleById(id);
    }

    @Override
    public Sale selectSaleById(Integer id) {
        String result="select * from sale where id=?";
        return JdbcUtil.executeQuerySingle(result,handler,id);

    }

    @Override
    public int updateSale(Sale sale) {
        //id都为空直接抛出异常
        if(Objects.isNull(sale.getId())){
            //抛出自定义的运行时异常
            throw new JdbcException("id is null");
        }
        //1=1没有意义，为了防止没有set语法出错
        StringBuilder sql=new StringBuilder("update purchase set id=id,");
        //不确定有几个？，创建动态数组，存储sql参数
        List<Object> params=new ArrayList<>();
        //拼接字符串首行保留空格，结尾保留逗号
        if(Objects.nonNull(sale.getProductId())){
            sql.append(" product_id=?,");
            params.add(sale.getProductId());
        }
        if(Objects.nonNull(sale.getQuantity())){
            sql.append(" quantity=?,");
            params.add(sale.getQuantity());
        }
        if(Objects.nonNull(sale.getSalePrice())){
            sql.append(" sale_price=?,");
            params.add(sale.getSalePrice());
        }
        if(Objects.nonNull(sale.getOperatorID())){
            sql.append(" operator_id=?,");
            params.add(sale.getOperatorID());
        }
        if(Objects.nonNull(sale.getSaleDate())){
            sql.append(" sale_time=?,");
            params.add(sale.getSaleDate());
        }

        //去除最后一个逗号
//        sql= new StringBuilder(sql.substring(0, sql.length() - 1));
        //确保多余的，在最后一个符号上
        sql.deleteCharAt(sql.length()-1);
        //拼接where条件
        sql.append(" where id=?");
        params.add(sale.getId());
        return JdbcUtil.executeUpdate(sql.toString(), params.toArray());
    }

    @Override
    public int deleteSale(Integer id) {
        String sql="delete from sale where id = ?";
        return JdbcUtil.executeUpdate(sql,id);

    }

    @Override
    public List<Sale> selectSalesByPage(int pageNum, int pageSize) {
        //参数校验
        if(pageNum<1){
            pageNum=1;
        }
        if(pageSize<1){
            pageSize=10;
        }
        //第一个问号是pagesize每页数量 往后查几行 第二个问号是offset偏移量 第几行开始查
        String sql="select * from sale limit ? offset ?";
        int offset=(pageNum-1)*pageSize;
        return JdbcUtil.executeQueryList(sql,handler,pageSize,offset);

    }

    @Override
    public List<Sale> searchSales(Integer productId, Integer operatorId, LocalDateTime startTime, LocalDateTime endTime) {
        //1.基础SQL
        StringBuilder sql = new StringBuilder("SELECT * FROM sale WHERE 1=1");
        //用动态数组存放动态参数
        List<Object> params = new ArrayList<>();
        if(Objects.nonNull(productId)){
            sql.append(" and product_id=?");
            params.add(productId);
        }
        if(Objects.nonNull(operatorId)){
            sql.append(" and operator_id=?");
            params.add(operatorId);
        }
        if (startTime != null && endTime != null) {
            //调用compareTo方法比较
            if (startTime.compareTo(endTime) > 0) {
                //抛出非法参数异常
                throw new IllegalArgumentException("起始日期不能超过结束日期");
            }
            //拼接价格区间查询条件
            sql.append(" AND sale_time BETWEEN ? AND ?");
            params.add(startTime);
            params.add(endTime);
        } else {

            if (startTime != null) {
                sql.append(" AND sale_time >= ?");
                params.add(startTime);
            }
            if (endTime != null) {
                sql.append(" AND sale_time <= ?");
                params.add(endTime);
            }
        }

        //3.执行查询，映射查询结果
        return JdbcUtil.executeQueryList(sql.toString(), handler, params.toArray());

    }

    @Override
    public int sumQuantityByProductId(Integer productId) {
        String sql="select sum(quantity) from sale where product_id=?";
        return JdbcUtil.executeQuerySingle(sql,rs->rs.getInt(1),productId);
    }

    @Override
    public BigDecimal sumRevenue(LocalDateTime start, LocalDateTime end) {
        StringBuilder sql = new StringBuilder("SELECT sum(quantity*sale_price) FROM sale WHERE 1=1");
        List<Object> params = new ArrayList<>();
        if (start != null && end != null) {
            //调用compareTo方法比较
            if (start.compareTo(end) > 0) {
                //抛出非法参数异常
                throw new IllegalArgumentException("起始日期不能超过结束日期");
            }
            //拼接价格区间查询条件
            sql.append(" AND sale_time BETWEEN ? AND ?");
            params.add(start);
            params.add(end);
        } else {

            if (start != null) {
                sql.append(" AND sale_time >= ?");
                params.add(start);
            }
            if (end != null) {
                sql.append(" AND sale_time <= ?");
                params.add(end);
            }
        }
        return JdbcUtil.executeQuerySingle(sql.toString(), rs -> rs.getBigDecimal(1), params.toArray());

    }

    @Override
    public Map<Integer, Integer> countSalesByOperator() {
        String sql="select operator_id from sale";
        List<Integer> sp = JdbcUtil.executeQueryList(sql, rs -> rs.getInt(1));
        String sql1="select sum(quantity) from sale where operator_id=?";
        Map<Integer,Integer> map = new HashMap<>();
        for (Integer i : sp) {
            map.put(i,JdbcUtil.executeQuerySingle(sql1,rs -> rs.getInt(1),i));
        }
        return map;
    }
}
